<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta name="referrer" content="no-referrer"><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"><title>15.Python之Mysql数据库相关 | 小妖笔记</title><meta name="author" content="小妖"><meta name="copyright" content="小妖"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="Python第二阶段 - MYSQL数据库相关 章节目录 [!WARNING|label:本章需要学习的内容]   DDL  库管理 DDL  表管理  1.DDL 库+表管理DDL【库】管理  SQL代码演示   1234567891011121314# 我是注释 查看数据库 show DATABASES;&#x2F;* 使用数据库  use 数据库名; *&#x2F;USE emlog;# 创建数据库CREATE">
<meta property="og:type" content="article">
<meta property="og:title" content="15.Python之Mysql数据库相关">
<meta property="og:url" content="http://grace.666443.xyz/posts/2b5a0751.html">
<meta property="og:site_name" content="小妖笔记">
<meta property="og:description" content="Python第二阶段 - MYSQL数据库相关 章节目录 [!WARNING|label:本章需要学习的内容]   DDL  库管理 DDL  表管理  1.DDL 库+表管理DDL【库】管理  SQL代码演示   1234567891011121314# 我是注释 查看数据库 show DATABASES;&#x2F;* 使用数据库  use 数据库名; *&#x2F;USE emlog;# 创建数据库CREATE">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://p.ananas.chaoxing.com/star3/origin/2a1821704670b5cf8c2289255e183d62.png">
<meta property="article:published_time" content="2022-12-08T12:17:02.000Z">
<meta property="article:modified_time" content="2022-12-08T12:17:26.600Z">
<meta property="article:author" content="小妖">
<meta property="article:tag" content="笔记 开发">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://p.ananas.chaoxing.com/star3/origin/2a1821704670b5cf8c2289255e183d62.png"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="http://grace.666443.xyz/posts/2b5a0751"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: {"path":"/search.xml","preload":true,"languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#1f1f1f","position":"bottom-left"},
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: '15.Python之Mysql数据库相关',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2022-12-08 20:17:26'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 5.4.2"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">28</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">2</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">7</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-heartbeat"></i><span> 清单</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/photos/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://p.ananas.chaoxing.com/star3/origin/2a1821704670b5cf8c2289255e183d62.png')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">小妖笔记</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-heartbeat"></i><span> 清单</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/photos/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">15.Python之Mysql数据库相关</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="fa-fw post-meta-icon far fa-calendar-alt"></i><span class="post-meta-label">发表于</span><time datetime="2022-12-08T12:17:02.000Z" title="发表于 2022-12-08 20:17:02">2022-12-08</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/python%E5%9F%BA%E7%A1%80/">python基础</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="15.Python之Mysql数据库相关"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="Python第二阶段-MYSQL数据库相关"><a href="#Python第二阶段-MYSQL数据库相关" class="headerlink" title="Python第二阶段 - MYSQL数据库相关"></a>Python第二阶段 - MYSQL数据库相关</h1><hr>
<h2 id="章节目录"><a href="#章节目录" class="headerlink" title="章节目录"></a><strong>章节目录</strong></h2><blockquote>
<p>[!WARNING|label:本章需要学习的内容]</p>
</blockquote>
<ul>
<li>DDL  库管理</li>
<li>DDL  表管理</li>
</ul>
<h1 id="1-DDL-库-表管理"><a href="#1-DDL-库-表管理" class="headerlink" title="1.DDL 库+表管理"></a><strong>1.DDL 库+表管理</strong></h1><h2 id="DDL【库】管理"><a href="#DDL【库】管理" class="headerlink" title="DDL【库】管理"></a><strong>DDL【库】管理</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/90bbdac778a88497a28b2d057654a248.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/a058fdb95a28c6d4c35a05faf33da0d6.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/46f456e28cca2bdde85d5f3ee9f7032c.png" alt="3"></p>
<blockquote>
<p>SQL代码演示</p>
</blockquote>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># 我是注释 查看数据库 </span><br><span class="line"><span class="keyword">show</span> DATABASES;</span><br><span class="line"></span><br><span class="line"><span class="comment">/* 使用数据库  use 数据库名; */</span></span><br><span class="line">USE emlog;</span><br><span class="line"></span><br><span class="line"># 创建数据库</span><br><span class="line"><span class="keyword">CREATE</span> DATABASE python_1 CHARSET utf8;</span><br><span class="line"></span><br><span class="line"># 删除数据库</span><br><span class="line"><span class="keyword">DROP</span> DATABASE python_1;</span><br><span class="line"></span><br><span class="line"># 查看当前使用的数据库 </span><br><span class="line"><span class="keyword">SELECT</span> DATABASE();</span><br></pre></td></tr></table></figure>

<h2 id="DDL【表】管理"><a href="#DDL【表】管理" class="headerlink" title="DDL【表】管理"></a><strong>DDL【表】管理</strong></h2><p> <img src="https://p.ananas.chaoxing.com/star3/origin/4e88ca354e95043b35b24704b9422d2e.png" alt="3"><br> <img src="https://p.ananas.chaoxing.com/star3/origin/a5db9496b0ced846eaab7df2a7a1df28.png" alt="4"></p>
<blockquote>
<p>表操作 代码演示</p>
</blockquote>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"># 数据库名称 python_1</span><br><span class="line"></span><br><span class="line"># 选择哪个表 </span><br><span class="line">use python_1;</span><br><span class="line"></span><br><span class="line"># 查看有哪些表 </span><br><span class="line"><span class="keyword">show</span> tables;</span><br><span class="line"></span><br><span class="line"># 创建表 </span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">table</span> student(</span><br><span class="line">	id <span class="type">int</span>,</span><br><span class="line">	name <span class="type">varchar</span>(<span class="number">10</span>),</span><br><span class="line">	age <span class="type">int</span></span><br><span class="line">);</span><br><span class="line"> </span><br><span class="line"># 删除表 </span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> student;</span><br></pre></td></tr></table></figure>

<p><img src="https://p.ananas.chaoxing.com/star3/origin/5fcbb4fb64974a29de221adc58e10c9c.png" alt="5"></p>
<h1 id="2-DML数据库操作"><a href="#2-DML数据库操作" class="headerlink" title="2.DML数据库操作"></a><strong>2.DML数据库操作</strong></h1><p><img src="https://p.ananas.chaoxing.com/star3/origin/32233b2b353b24160fc7bea84f933eaf.png" alt="1"></p>
<h2 id="基础语法-Insert插入"><a href="#基础语法-Insert插入" class="headerlink" title="基础语法 - Insert插入"></a><strong>基础语法 - Insert插入</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/f2874c797c5fb91d3cdfaaf6dcb44c60.png" alt="2"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 演示：插入数据 </span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student(id) <span class="keyword">VALUES</span>(<span class="number">1</span>),(<span class="number">2</span>),(<span class="number">3</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student <span class="keyword">VALUES</span> (<span class="number">4</span>,<span class="string">&#x27;周杰伦a&#x27;</span>,<span class="number">31</span>) , (<span class="number">5</span>,<span class="string">&#x27;订单&#x27;</span>,<span class="number">40</span>);</span><br></pre></td></tr></table></figure>

<h2 id="基础语法-Delete删除"><a href="#基础语法-Delete删除" class="headerlink" title="基础语法 - Delete删除"></a><strong>基础语法 - Delete删除</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/9f984323d07c853e535fbb8c8feead6b.png" alt="3"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 删除数据 通过<span class="keyword">where</span>条件</span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> student <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h2 id="基础语法-Update更新"><a href="#基础语法-Update更新" class="headerlink" title="基础语法 - Update更新"></a><strong>基础语法 - Update更新</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/311e53acd3239c29e2a5eba13b19a911.png" alt="4"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 更新 </span><br><span class="line"><span class="keyword">UPDATE</span> student <span class="keyword">set</span> name<span class="operator">=</span><span class="string">&#x27;哈哈哈&#x27;</span> <span class="keyword">where</span> id <span class="operator">=</span>  <span class="number">2</span> ;</span><br><span class="line"> </span><br></pre></td></tr></table></figure>

<p><img src="https://p.ananas.chaoxing.com/star3/origin/5b5d8ddc40520dbb781f52ca943afc99.png" alt="总结"></p>
<h1 id="3-DQL数据查询"><a href="#3-DQL数据查询" class="headerlink" title="3.DQL数据查询"></a><strong>3.DQL数据查询</strong></h1><h2 id="1-基础查询"><a href="#1-基础查询" class="headerlink" title="1.基础查询"></a><strong>1.基础查询</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/81948049bbe3615a0e32ac2dfe6c9b1d.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/f8c49f0916eeb28d4ca5df647a213b64.png" alt="3"></p>
<h2 id="2-分组聚合"><a href="#2-分组聚合" class="headerlink" title="2.分组聚合"></a><strong>2.分组聚合</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/987109bc171bb756890cad85b35db64f.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/8090bd41582759c559bc1107d90e8541.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/eab9f55a7b2352ee11d2e2467c416369.png" alt="3"><br><img src="https://p.ananas.chaoxing.com/star3/origin/e9c838313a86987c9a4ebda0b287d79c.png" alt="4"><br><img src="https://p.ananas.chaoxing.com/star3/origin/237f01a82cca7928d26b08883af004d7.png" alt="5"></p>
<h2 id="3-排序分页"><a href="#3-排序分页" class="headerlink" title="3.排序分页"></a><strong>3.排序分页</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/30695136308d209b9b737500bb2c8242.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/461e3b8518c0d8115b533f7ba9206d47.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/1ea858ea0c64c3f91c46f68caef09dc0.png" alt="3"><br><img src="https://p.ananas.chaoxing.com/star3/origin/44d438310a4714359b23d147e3771f5d.png" alt="4"><br><img src="https://p.ananas.chaoxing.com/star3/origin/6ff856104a7b13a654f941ed8073f046.png" alt="5"></p>
<h1 id="4-Python操作MySQL"><a href="#4-Python操作MySQL" class="headerlink" title="4.Python操作MySQL"></a><strong>4.Python操作MySQL</strong></h1><h2 id="基础使用"><a href="#基础使用" class="headerlink" title="基础使用"></a><strong>基础使用</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/510cc701afca1ea4a438ad7a721cda3b.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/e2c6ad0d4735848a6abb1af7ac177dbf.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/53da1a3d5cf11ad876488cdb7d373d8b.png" alt="3"><br><img src="https://p.ananas.chaoxing.com/star3/origin/606b98874af71c89fcd45b113b58daf2.png" alt="4"><br><img src="https://p.ananas.chaoxing.com/star3/origin/4bb2a2a769b885ada77ac06e6681d2bc.png" alt="5"><br><img src="https://p.ananas.chaoxing.com/star3/origin/314d7a7d0cfa57d4a41ea801d3a4b5f8.png" alt="6"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">&quot;&quot;&quot;</span><br><span class="line">演示 python pymysql库的基础操作</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line"></span><br><span class="line"># 需要先导包</span><br><span class="line"><span class="keyword">from</span> pymysql import Connection</span><br><span class="line"></span><br><span class="line"># 构建到MYSQL数据库的链接</span><br><span class="line">conn <span class="operator">=</span> Connection(</span><br><span class="line">    host<span class="operator">=</span>&quot;localhost&quot;,   # 主机名(IP)</span><br><span class="line">    port<span class="operator">=</span><span class="number">3306</span>,          # 端口</span><br><span class="line">    <span class="keyword">user</span><span class="operator">=</span>&quot;root&quot;,        # 账户</span><br><span class="line">    password<span class="operator">=</span>&quot;root&quot;     # 密码</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># print(conn.get_server_info())</span><br><span class="line"></span><br><span class="line"># 执行非查询性质<span class="keyword">SQL</span></span><br><span class="line"></span><br><span class="line"># 获取到游标对象</span><br><span class="line"><span class="keyword">cursor</span> <span class="operator">=</span> conn.cursor()</span><br><span class="line"></span><br><span class="line"># 选择数据库</span><br><span class="line">conn.select_db(&quot;python_1&quot;)</span><br><span class="line"></span><br><span class="line"># 执行<span class="keyword">sql</span></span><br><span class="line"># cursor.execute(&quot;create table test1(id int);&quot;)</span><br><span class="line"></span><br><span class="line"># 执行查询性质<span class="keyword">SQL</span></span><br><span class="line">cursor.execute(&quot;select * from student&quot;)</span><br><span class="line"></span><br><span class="line"><span class="keyword">result</span> <span class="operator">=</span> cursor.fetchall()</span><br><span class="line"></span><br><span class="line"><span class="keyword">for</span> item <span class="keyword">in</span> <span class="keyword">result</span>:</span><br><span class="line">    print(item)</span><br><span class="line"></span><br><span class="line"># 关闭链接</span><br><span class="line">conn.close()</span><br></pre></td></tr></table></figure>

<h2 id="数据插入"><a href="#数据插入" class="headerlink" title="数据插入"></a><strong>数据插入</strong></h2><p><img src="https://p.ananas.chaoxing.com/star3/origin/34a9af8f09f5ba0490ba6f2df6b75e67.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/3d64bcce85e68cf91f13eeb8869ceb8d.png" alt="2"></p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">演示 python pymysql库的基础操作</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 需要先导包</span></span><br><span class="line"><span class="keyword">from</span> pymysql <span class="keyword">import</span> Connection</span><br><span class="line"></span><br><span class="line"><span class="comment"># 构建到MYSQL数据库的链接</span></span><br><span class="line">conn = Connection(</span><br><span class="line">    host=<span class="string">&quot;localhost&quot;</span>,   <span class="comment"># 主机名(IP)</span></span><br><span class="line">    port=<span class="number">3306</span>,          <span class="comment"># 端口</span></span><br><span class="line">    user=<span class="string">&quot;root&quot;</span>,        <span class="comment"># 账户</span></span><br><span class="line">    password=<span class="string">&quot;root&quot;</span>     <span class="comment"># 密码</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment"># print(conn.get_server_info())</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行非查询性质SQL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取到游标对象</span></span><br><span class="line">cursor = conn.cursor()</span><br><span class="line"></span><br><span class="line"><span class="comment"># 选择数据库</span></span><br><span class="line">conn.select_db(<span class="string">&quot;python_1&quot;</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行sql</span></span><br><span class="line"><span class="comment"># cursor.execute(&quot;create table test1(id int);&quot;)</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行查询性质SQL</span></span><br><span class="line">cursor.execute(<span class="string">&quot;select * from student&quot;</span>)</span><br><span class="line"></span><br><span class="line">result = cursor.fetchall()</span><br><span class="line"></span><br><span class="line"><span class="keyword">for</span> item <span class="keyword">in</span> result:</span><br><span class="line">    <span class="built_in">print</span>(item)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 操作添加插入</span></span><br><span class="line">cursor.execute(<span class="string">&quot;insert into student values(10001,&#x27;李四&#x27;,20),(10002,&#x27;李四2&#x27;,22),(10003,&#x27;李四3&#x27;,23),(10004,&#x27;李四4&#x27;,24);&quot;</span>)</span><br><span class="line"><span class="comment"># 执行提交 才能添加</span></span><br><span class="line">conn.commit()</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 关闭链接</span></span><br><span class="line">conn.close()</span><br></pre></td></tr></table></figure>

<p><img src="https://p.ananas.chaoxing.com/star3/origin/554452b7342d9dfffe46b1a40247181b.png" alt="3"></p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">演示 python pymysql库的基础操作</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 需要先导包</span></span><br><span class="line"><span class="keyword">from</span> pymysql <span class="keyword">import</span> Connection</span><br><span class="line"></span><br><span class="line"><span class="comment"># 构建到MYSQL数据库的链接</span></span><br><span class="line">conn = Connection(</span><br><span class="line">    host=<span class="string">&quot;localhost&quot;</span>,   <span class="comment"># 主机名(IP)</span></span><br><span class="line">    port=<span class="number">3306</span>,          <span class="comment"># 端口</span></span><br><span class="line">    user=<span class="string">&quot;root&quot;</span>,        <span class="comment"># 账户</span></span><br><span class="line">    password=<span class="string">&quot;root&quot;</span>,    <span class="comment"># 密码</span></span><br><span class="line">    autocommit=<span class="literal">True</span>     <span class="comment"># 自动提交( 确认 )</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment"># print(conn.get_server_info())</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行非查询性质SQL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 获取到游标对象</span></span><br><span class="line">cursor = conn.cursor()</span><br><span class="line"></span><br><span class="line"><span class="comment"># 选择数据库</span></span><br><span class="line">conn.select_db(<span class="string">&quot;python_1&quot;</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行sql</span></span><br><span class="line"><span class="comment"># cursor.execute(&quot;create table test1(id int);&quot;)</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 执行查询性质SQL</span></span><br><span class="line">cursor.execute(<span class="string">&quot;select * from student&quot;</span>)</span><br><span class="line"></span><br><span class="line">result = cursor.fetchall()</span><br><span class="line"></span><br><span class="line"><span class="keyword">for</span> item <span class="keyword">in</span> result:</span><br><span class="line">    <span class="built_in">print</span>(item)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 操作添加插入</span></span><br><span class="line">cursor.execute(<span class="string">&quot;insert into student values(10001,&#x27;李四&#x27;,20),(10002,&#x27;李四2&#x27;,22),(10003,&#x27;李四3&#x27;,23),(10004,&#x27;李四4&#x27;,24);&quot;</span>)</span><br><span class="line"><span class="comment"># 执行提交 才能添加 【设置过自动提交，则不需要此处手动提交动作】</span></span><br><span class="line"><span class="comment"># conn.commit() </span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 关闭链接</span></span><br><span class="line">conn.close()</span><br></pre></td></tr></table></figure>

<p><img src="https://p.ananas.chaoxing.com/star3/origin/5f5c788e2b5891ae20e130c1cf15656b.png" alt="4"></p>
<h1 id="5-综合案例"><a href="#5-综合案例" class="headerlink" title="5.综合案例"></a><strong>5.综合案例</strong></h1><p><img src="https://p.ananas.chaoxing.com/star3/origin/58b61a05a37d0db519d67ea511381603.png" alt="1"><br><img src="https://p.ananas.chaoxing.com/star3/origin/05f0f870c6278fa4fc1fbbaca748b93c.png" alt="2"><br><img src="https://p.ananas.chaoxing.com/star3/origin/57efcff62fe2d4e1f8ca8f3f1240dcef.png" alt="3"><br><img src="https://p.ananas.chaoxing.com/star3/origin/a6376ea9885093c82c2291a30e077178.png" alt="4"><br><img src="https://p.ananas.chaoxing.com/star3/origin/0fe6aafa99020b070a1aa4fdfa99c8dd.png" alt="5"><br><img src="https://p.ananas.chaoxing.com/star3/origin/35619e606b39dd5bf1c717f2ebee87e7.png" alt="6"></p>
<blockquote>
<p>创建数据库和表 的 SQL 语句</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> DATABASE py_sql CHARSET utf8;</span><br><span class="line"></span><br><span class="line">USE py_sql;</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> orders(</span><br><span class="line">	order_date <span class="type">date</span>,</span><br><span class="line">	order_id <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">	money <span class="type">int</span>,</span><br><span class="line">	province <span class="type">varchar</span>(<span class="number">10</span>)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<blockquote>
<p>[!WARNING|label:此项目文件为3个文件，分别为] main.py <br>data_define.py <br> file_define.py <br></p>
</blockquote>
<blockquote>
<p>数据高速网盘下载</p>
</blockquote>
<p><a target="_blank" rel="noopener" href="https://sharewh1.xuexi365.com/share/download/f026065ae13981301763048980016929">2011年1月销售数据.txt</a></p>
<p><a target="_blank" rel="noopener" href="https://sharewh1.xuexi365.com/share/download/bad161d366413e196c1a666854428bd7">2011年2月销售数据JSON.txt</a></p>
<blockquote>
<p>py 执行代码  main.py</p>
</blockquote>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">面向对象，数据分析案例，主业务逻辑代码</span></span><br><span class="line"><span class="string">实现步骤</span></span><br><span class="line"><span class="string">1、设计一个类，可以完成数据的封装</span></span><br><span class="line"><span class="string">2、设计一个抽象类，定义文件读取的相关功能，并使用子类实现具体功能</span></span><br><span class="line"><span class="string">3、读取文件，生产数据对象</span></span><br><span class="line"><span class="string">4、进行数据需求的逻辑计算（计算每一天的销售额）</span></span><br><span class="line"><span class="string">5、通过PyEcharts进行图形绘制</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 先导包</span></span><br><span class="line"><span class="keyword">from</span> file_define <span class="keyword">import</span> FileReader, TextFileReader, JsonFileReader</span><br><span class="line"><span class="keyword">from</span> data_define <span class="keyword">import</span> Record</span><br><span class="line"><span class="keyword">from</span> pymysql <span class="keyword">import</span> Connection</span><br><span class="line"></span><br><span class="line"><span class="comment"># 1、打开数据文件</span></span><br><span class="line">text_file_reader = TextFileReader(<span class="string">&quot;/Volumes/E盘/资料/数据分析案例/2011年1月销售数据.txt&quot;</span>)</span><br><span class="line">json_file_reader = JsonFileReader(<span class="string">&quot;/Volumes/E盘/资料/数据分析案例/2011年2月销售数据JSON.txt&quot;</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 2、分别读取一月和二月的文件内容</span></span><br><span class="line">jan_data = text_file_reader.read_data()  <span class="comment"># type: <span class="built_in">list</span>[Record]</span></span><br><span class="line">fen_data = json_file_reader.read_data()  <span class="comment"># type: <span class="built_in">list</span>[Record]</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 3、将2个月份的数据合并为1个list来存储</span></span><br><span class="line">all_data: <span class="built_in">list</span>[Record] = jan_data + fen_data</span><br><span class="line"></span><br><span class="line"><span class="comment"># 构建MySQL链接对象</span></span><br><span class="line">conn = Connection(</span><br><span class="line">    host=<span class="string">&quot;localhost&quot;</span>,</span><br><span class="line">    port=<span class="number">3306</span>,</span><br><span class="line">    user=<span class="string">&quot;root&quot;</span>,</span><br><span class="line">    password=<span class="string">&quot;root&quot;</span>,</span><br><span class="line">    autocommit=<span class="literal">True</span></span><br><span class="line">)</span><br><span class="line"><span class="comment"># 获取游标对象</span></span><br><span class="line">cursor = conn.cursor()</span><br><span class="line"></span><br><span class="line"><span class="comment"># 选择数据库</span></span><br><span class="line">conn.select_db(<span class="string">&quot;py_sql&quot;</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 组织sql语句</span></span><br><span class="line"><span class="keyword">for</span> record <span class="keyword">in</span> all_data:</span><br><span class="line">    sql = <span class="string">f&quot;insert into orders(order_date,order_id,money,province)&quot;</span> \</span><br><span class="line">          <span class="string">f&quot;values (&#x27;<span class="subst">&#123;record.date&#125;</span>&#x27;,&#x27;<span class="subst">&#123;record.order_id&#125;</span>&#x27;,<span class="subst">&#123;record.money&#125;</span>,&#x27;<span class="subst">&#123;record.province&#125;</span>&#x27;)&quot;</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 执行SQL语句</span></span><br><span class="line">    cursor.execute(sql)</span><br><span class="line"></span><br><span class="line"><span class="comment"># 关闭sql链接</span></span><br><span class="line">conn.close()</span><br></pre></td></tr></table></figure>

<blockquote>
<p>data_define.py</p>
</blockquote>
<figure class="highlight py"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">数据定义的类</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 记录数据的基础信息</span></span><br><span class="line"><span class="keyword">class</span> <span class="title class_">Record</span>:</span><br><span class="line"></span><br><span class="line">    <span class="comment"># 多行数据同时点选快捷键，ALT + 鼠标选中</span></span><br><span class="line">    <span class="comment"># 通过构造函数赋初始值</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">__init__</span>(<span class="params">self,date,order_id,money,province</span>):</span><br><span class="line">        self.date = date            <span class="comment"># 订单日期</span></span><br><span class="line">        self.order_id = order_id    <span class="comment"># 订单ID</span></span><br><span class="line">        self.money = money          <span class="comment"># 订单金额</span></span><br><span class="line">        self.province = province    <span class="comment"># 销售省份</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 通过魔术方法 ，转变为字符串或者说直接被print输出的行为</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">__str__</span>(<span class="params">self</span>):</span><br><span class="line">        <span class="keyword">return</span> <span class="string">f&quot;<span class="subst">&#123;self.date&#125;</span>,<span class="subst">&#123;self.order_id&#125;</span>,<span class="subst">&#123;self.money&#125;</span>,<span class="subst">&#123;self.province&#125;</span>&quot;</span></span><br></pre></td></tr></table></figure>

<blockquote>
<p>file_define.py</p>
</blockquote>
<figure class="highlight py"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br></pre></td><td class="code"><pre><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="string">和文件相关的类定义</span></span><br><span class="line"><span class="string">&quot;&quot;&quot;</span></span><br><span class="line"><span class="keyword">import</span> json</span><br><span class="line"></span><br><span class="line"><span class="comment"># 先定义一个抽象类用来做顶层设计，确定有哪些功能需要实现</span></span><br><span class="line"><span class="keyword">from</span> data_define <span class="keyword">import</span> Record</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">class</span> <span class="title class_">FileReader</span>:</span><br><span class="line"></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">read_data</span>(<span class="params">self</span>) -&gt; <span class="built_in">list</span>[Record]:</span><br><span class="line">        <span class="string">&quot;&quot;&quot;读取文件的数据，读到的每一条数据都转换为Record对象，将它们都封装到list内返回即可&quot;&quot;&quot;</span></span><br><span class="line">        <span class="keyword">pass</span></span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">class</span> <span class="title class_">TextFileReader</span>(<span class="title class_ inherited__">FileReader</span>):</span><br><span class="line">    <span class="comment"># 构造函数</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">__init__</span>(<span class="params">self, path</span>):</span><br><span class="line">        self.path = path  <span class="comment"># 定义成员变量记录文件的路径</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 复写(实现抽象方法) 父类的方法</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">read_data</span>(<span class="params">self</span>) -&gt; <span class="built_in">list</span>[Record]:</span><br><span class="line">        <span class="comment"># 打开数据文件</span></span><br><span class="line">        f = <span class="built_in">open</span>(self.path, <span class="string">&#x27;r&#x27;</span>, encoding=<span class="string">&#x27;UTF-8&#x27;</span>)</span><br><span class="line"></span><br><span class="line">        <span class="comment"># 定义一个空列表，用于返回数据</span></span><br><span class="line">        record_list = []  <span class="comment"># type:<span class="built_in">list</span>[Record]</span></span><br><span class="line"></span><br><span class="line">        <span class="comment"># 读取文件</span></span><br><span class="line">        <span class="keyword">for</span> line <span class="keyword">in</span> f.readlines():</span><br><span class="line">            line = line.strip()  <span class="comment"># 去除首尾空格，或者去除读取每一行数据中的\n</span></span><br><span class="line">            data_list = line.split(<span class="string">&quot;,&quot;</span>)  <span class="comment"># 通过，分割，返回列表数据</span></span><br><span class="line">            record = Record(data_list[<span class="number">0</span>], data_list[<span class="number">1</span>], <span class="built_in">int</span>(data_list[<span class="number">2</span>]), data_list[<span class="number">3</span>])</span><br><span class="line">            record_list.append(record)</span><br><span class="line"></span><br><span class="line">        <span class="comment"># 关闭文件</span></span><br><span class="line">        f.close()</span><br><span class="line"></span><br><span class="line">        <span class="comment"># 返回数据</span></span><br><span class="line">        <span class="keyword">return</span> record_list</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">class</span> <span class="title class_">JsonFileReader</span>(<span class="title class_ inherited__">FileReader</span>):</span><br><span class="line">    <span class="comment"># 构造函数</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">__init__</span>(<span class="params">self, path</span>):</span><br><span class="line">        self.path = path  <span class="comment"># 定义成员变量记录文件的路径</span></span><br><span class="line"></span><br><span class="line">    <span class="comment"># 复写</span></span><br><span class="line">    <span class="keyword">def</span> <span class="title function_">read_data</span>(<span class="params">self</span>) -&gt; <span class="built_in">list</span>[Record]:</span><br><span class="line">        <span class="comment"># 打开数据文件</span></span><br><span class="line">        f = <span class="built_in">open</span>(self.path, <span class="string">&#x27;r&#x27;</span>, encoding=<span class="string">&#x27;UTF-8&#x27;</span>)</span><br><span class="line"></span><br><span class="line">        <span class="comment"># 定义一个空列表，用于返回数据</span></span><br><span class="line">        record_list = []  <span class="comment"># type:<span class="built_in">list</span>[Record]</span></span><br><span class="line"></span><br><span class="line">        <span class="comment"># 读取文件</span></span><br><span class="line">        <span class="keyword">for</span> line <span class="keyword">in</span> f.readlines():</span><br><span class="line">            data_dict = json.loads(line)</span><br><span class="line">            record = Record(data_dict[<span class="string">&#x27;date&#x27;</span>], data_dict[<span class="string">&#x27;order_id&#x27;</span>], <span class="built_in">int</span>(data_dict[<span class="string">&#x27;money&#x27;</span>]), data_dict[<span class="string">&#x27;province&#x27;</span>])</span><br><span class="line">            record_list.append(record)</span><br><span class="line"></span><br><span class="line">        <span class="comment"># 关闭文件</span></span><br><span class="line">        f.close()</span><br><span class="line">        <span class="comment"># 返回数据</span></span><br><span class="line">        <span class="keyword">return</span> record_list</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 测试</span></span><br><span class="line"><span class="keyword">if</span> __name__ == <span class="string">&#x27;__main__&#x27;</span>:</span><br><span class="line">    text_file_reader = TextFileReader(<span class="string">&quot;/Volumes/E盘/资料/数据分析案例/2011年1月销售数据.txt&quot;</span>)</span><br><span class="line">    Json_file_reader = JsonFileReader(<span class="string">&quot;/Volumes/E盘/资料/数据分析案例/2011年2月销售数据JSON.txt&quot;</span>)</span><br><span class="line">    list_1 = text_file_reader.read_data()</span><br><span class="line">    list_2 = Json_file_reader.read_data()</span><br><span class="line"></span><br><span class="line">    <span class="keyword">for</span> i <span class="keyword">in</span> list_1:</span><br><span class="line">        <span class="built_in">print</span>(i)</span><br><span class="line"></span><br><span class="line">    <span class="keyword">for</span> l <span class="keyword">in</span>  list_2:</span><br><span class="line">        <span class="built_in">print</span>(l)</span><br></pre></td></tr></table></figure>

<blockquote>
<p>总结</p>
</blockquote>
<p><img src="https://p.ananas.chaoxing.com/star3/origin/e41cfea40a2dd67c9b857a6792414a3c.png" alt="7"></p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="http://grace.666443.xyz">小妖</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://grace.666443.xyz/posts/2b5a0751.html">http://grace.666443.xyz/posts/2b5a0751.html</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://grace.666443.xyz" target="_blank">小妖笔记</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"></div><div class="post_share"><div class="social-share" data-image="https://p.ananas.chaoxing.com/star3/origin/2a1821704670b5cf8c2289255e183d62.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="https://p.ananas.chaoxing.com/star3/origin/5a01db3c5a0daca9fa2b024736bf66e3.png" target="_blank"><img class="post-qr-code-img" src="https://p.ananas.chaoxing.com/star3/origin/5a01db3c5a0daca9fa2b024736bf66e3.png" alt="微信打赏"/></a><div class="post-qr-code-desc">微信打赏</div></li><li class="reward-item"><a href="https://p.ananas.chaoxing.com/star3/origin/afffb275198370656cb6cef6a1107185.png" target="_blank"><img class="post-qr-code-img" src="https://p.ananas.chaoxing.com/star3/origin/afffb275198370656cb6cef6a1107185.png" alt="支付宝打赏"/></a><div class="post-qr-code-desc">支付宝打赏</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/posts/5038ed.html"><img class="prev-cover" src="https://p.ananas.chaoxing.com/star3/origin/2054c03c91d338203c730610ead1b303.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">16.Python爬虫-基础</div></div></a></div><div class="next-post pull-right"><a href="/posts/6ebaa02b.html"><img class="next-cover" src="https://p.ananas.chaoxing.com/star3/origin/d24d9bfcd4ce3f910d6b5cdc78676d75.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">14.Python面向对象</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">小妖</div><div class="author-info__description">常用开发中的笔记记录</div></div><div class="card-info-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">28</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">2</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">7</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">This is my Blog 技术笔记</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#Python%E7%AC%AC%E4%BA%8C%E9%98%B6%E6%AE%B5-MYSQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9B%B8%E5%85%B3"><span class="toc-number">1.</span> <span class="toc-text">Python第二阶段 - MYSQL数据库相关</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%AB%A0%E8%8A%82%E7%9B%AE%E5%BD%95"><span class="toc-number">1.1.</span> <span class="toc-text">章节目录</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#1-DDL-%E5%BA%93-%E8%A1%A8%E7%AE%A1%E7%90%86"><span class="toc-number">2.</span> <span class="toc-text">1.DDL 库+表管理</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#DDL%E3%80%90%E5%BA%93%E3%80%91%E7%AE%A1%E7%90%86"><span class="toc-number">2.1.</span> <span class="toc-text">DDL【库】管理</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DDL%E3%80%90%E8%A1%A8%E3%80%91%E7%AE%A1%E7%90%86"><span class="toc-number">2.2.</span> <span class="toc-text">DDL【表】管理</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#2-DML%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C"><span class="toc-number">3.</span> <span class="toc-text">2.DML数据库操作</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E7%A1%80%E8%AF%AD%E6%B3%95-Insert%E6%8F%92%E5%85%A5"><span class="toc-number">3.1.</span> <span class="toc-text">基础语法 - Insert插入</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E7%A1%80%E8%AF%AD%E6%B3%95-Delete%E5%88%A0%E9%99%A4"><span class="toc-number">3.2.</span> <span class="toc-text">基础语法 - Delete删除</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E7%A1%80%E8%AF%AD%E6%B3%95-Update%E6%9B%B4%E6%96%B0"><span class="toc-number">3.3.</span> <span class="toc-text">基础语法 - Update更新</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#3-DQL%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2"><span class="toc-number">4.</span> <span class="toc-text">3.DQL数据查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-%E5%9F%BA%E7%A1%80%E6%9F%A5%E8%AF%A2"><span class="toc-number">4.1.</span> <span class="toc-text">1.基础查询</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-%E5%88%86%E7%BB%84%E8%81%9A%E5%90%88"><span class="toc-number">4.2.</span> <span class="toc-text">2.分组聚合</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-%E6%8E%92%E5%BA%8F%E5%88%86%E9%A1%B5"><span class="toc-number">4.3.</span> <span class="toc-text">3.排序分页</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#4-Python%E6%93%8D%E4%BD%9CMySQL"><span class="toc-number">5.</span> <span class="toc-text">4.Python操作MySQL</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%9F%BA%E7%A1%80%E4%BD%BF%E7%94%A8"><span class="toc-number">5.1.</span> <span class="toc-text">基础使用</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E6%8F%92%E5%85%A5"><span class="toc-number">5.2.</span> <span class="toc-text">数据插入</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#5-%E7%BB%BC%E5%90%88%E6%A1%88%E4%BE%8B"><span class="toc-number">6.</span> <span class="toc-text">5.综合案例</span></a></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/posts/18ae4187.html" title="Python项目0002 - 超星小组云盘批量上传图片"><img src="http://pic.qqbizhi.com/allimg/bbpic/92/3692_4.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="Python项目0002 - 超星小组云盘批量上传图片"/></a><div class="content"><a class="title" href="/posts/18ae4187.html" title="Python项目0002 - 超星小组云盘批量上传图片">Python项目0002 - 超星小组云盘批量上传图片</a><time datetime="2022-12-10T15:52:50.000Z" title="发表于 2022-12-10 23:52:50">2022-12-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/posts/fe5af520.html" title="Python项目0001 - B站视频无水印解析"><img src="https://pic1.zhimg.com/v2-1b0b3d48f5259a87189012f8d7e9b370_r.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="Python项目0001 - B站视频无水印解析"/></a><div class="content"><a class="title" href="/posts/fe5af520.html" title="Python项目0001 - B站视频无水印解析">Python项目0001 - B站视频无水印解析</a><time datetime="2022-12-10T15:17:28.000Z" title="发表于 2022-12-10 23:17:28">2022-12-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/posts/3f24dd85.html" title="05.hexo视频教程"><img src="https://i1.hdslb.com/bfs/archive/cf6a8413b8ec8bd17612c2b48055be269b7ff496.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="05.hexo视频教程"/></a><div class="content"><a class="title" href="/posts/3f24dd85.html" title="05.hexo视频教程">05.hexo视频教程</a><time datetime="2022-12-09T16:26:08.000Z" title="发表于 2022-12-10 00:26:08">2022-12-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/posts/afee721f.html" title="0001.超星网盘直链"><img src="http://p.ananas.chaoxing.com/star3/origin/6ed6e3f7288e00f92ed58e6e018e76cd.jpg?rw=640&amp;rh=258" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="0001.超星网盘直链"/></a><div class="content"><a class="title" href="/posts/afee721f.html" title="0001.超星网盘直链">0001.超星网盘直链</a><time datetime="2022-12-09T16:19:40.000Z" title="发表于 2022-12-10 00:19:40">2022-12-10</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/posts/e76c3612.html" title="03.hexo文章标签配置"><img src="http://img.netbian.com/file/20150311/018c085855710a01c71d88a48a3270fe.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="03.hexo文章标签配置"/></a><div class="content"><a class="title" href="/posts/e76c3612.html" title="03.hexo文章标签配置">03.hexo文章标签配置</a><time datetime="2022-12-08T13:47:38.000Z" title="发表于 2022-12-08 21:47:38">2022-12-08</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2022 By 小妖</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div><div class="footer_custom_text"><a target="_blank" rel="noopener" href='https://butterfly.js.org/'>Butterfly主题配置</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据库加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div id="local-search-results"></div></div></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.min.js"></script><script src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script src="/js/search/local-search.js"></script><div class="js-pjax"><script>(() => {
  const $mermaidWrap = document.querySelectorAll('#article-container .mermaid-wrap')
  if ($mermaidWrap.length) {
    window.runMermaid = () => {
      window.loadMermaid = true
      const theme = document.documentElement.getAttribute('data-theme') === 'dark' ? 'dark' : 'default'

      Array.from($mermaidWrap).forEach((item, index) => {
        const mermaidSrc = item.firstElementChild
        const mermaidThemeConfig = '%%{init:{ \'theme\':\'' + theme + '\'}}%%\n'
        const mermaidID = 'mermaid-' + index
        const mermaidDefinition = mermaidThemeConfig + mermaidSrc.textContent
        mermaid.mermaidAPI.render(mermaidID, mermaidDefinition, (svgCode) => {
          mermaidSrc.insertAdjacentHTML('afterend', svgCode)
        })
      })
    }

    const loadMermaid = () => {
      window.loadMermaid ? runMermaid() : getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(runMermaid)
    }

    window.pjax ? loadMermaid() : document.addEventListener('DOMContentLoaded', loadMermaid)
  }
})()</script></div><script defer="defer" id="ribbon" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="false"></script><div class="fb-customerchat" id="fb-customer-chat" attribution="biz_inbox"></div><script>document.getElementById('fb-root') ? '' : document.body.insertAdjacentHTML('afterend', '<div id="fb-root"></div>')

window.fbAsyncInit = function() {
  FB.init({
    xfbml: true,
    version: 'v15.0'
  });
};

(function(d, s, id) {
  var js, fjs = d.getElementsByTagName(s)[0];
  if (d.getElementById(id)) return;
  js = d.createElement(s); js.id = id;
  js.src = 'https://connect.facebook.net/zh_CN/sdk/xfbml.customerchat.js';
  fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));

if (false) {
  var chatBtnFn = () => {
    var chatBtn = document.getElementById("chat_btn")
    chatBtn.addEventListener("click", function(){
      FB.CustomerChat.show();
    });
  }
  chatBtnFn()
} else {
  if (false) {
    function chatBtnHide () {
      FB.CustomerChat.hide()
    }
    function chatBtnShow () {
      FB.CustomerChat.show(false)
    }
  }
}</script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>